import numpy
import pymysql
import tushare



def fundamental(table_name,year,season):
    fundamental_db = pymysql.connect('localhost', 'root', 'minicooper','fundamental', charset='utf8')
    fundamental_cursor = fundamental_db.cursor()

    create_sql = """CREATE TABLE IF NOT EXISTS `{0}` (
                                                                adratio float,
                                                                arturndays float,
                                                                arturnover float,
                                                                bips float,
                                                                business_income float,
                                                                bvps float,
                                                                cashflowratio float,
                                                                cashratio float,
                                                                cf_liabilities float,
                                                                cf_nm float,
                                                                cf_sales float,
                                                                code char(6),
                                                                currentasset_days float,
                                                                currentasset_turnover float,
                                                                currentratio float,
                                                                distrib text,
                                                                epcf float,
                                                                eps_yoy float,
                                                                epsg float,
                                                                eps float,
                                                                gross_profit_rate float,
                                                                icratio float,
                                                                inventory_days float,
                                                                inventory_turnover float,
                                                                mbrg float,
                                                                name text,
                                                                nav float,
                                                                net_profit_ratio float,
                                                                net_profits float,
                                                                nprg float,
                                                                profits_yoy float,
                                                                quickratio float,
                                                                rateofreturn float,
                                                                report_date date,
                                                                roe float,
                                                                seg float,
                                                                sheqratio float,
                                                                targ float,
                                                                SEASONS CHAR(6)
                                                                )""".format(table_name)


    fundamental_cursor.execute(create_sql)
    fundamental_db.commit()

    #业绩数据
    report_data_insert = "INSERT INTO `{0}` (code,name,eps,eps_yoy,bvps,roe,epcf,net_profits,profits_yoy,distrib,report_date,SEASONS) VALUES ".format(table_name)
    report_data = tushare.get_report_data(year,season)
    for k in range(len(report_data)):
        data = report_data.iloc[k]
        code = data['code']
        name = data['name']
        eps = data['eps'] if type(data['eps']) == numpy.float64 else None
        eps_yoy = data['eps_yoy'] if type(data['eps_yoy']) == numpy.float64 else None
        bvps = data['bvps'] if type(data['bvps']) == numpy.float64 else None
        roe = data['roe'] if type(data['roe']) == numpy.float64 else None
        epcf = data['epcf'] if type(data['epcf']) == numpy.float64 else None
        net_profits = data['net_profits'] if type(data['net_profits']) == numpy.float64 else None
        profits_yoy = data['profits_yoy'] if type(data['profits_yoy']) == numpy.float64 else None
        distrib = data['distrib'] if type(data['distrib']) == numpy.float64 else None
        report_date = str(year) + '-'+ str(data['report_date'])
        seasons = str(year) + str(season)
        insert_report_data = (code,name,eps,eps_yoy,bvps,roe,epcf,net_profits,profits_yoy,distrib,report_date,seasons)
        report_data_insert += str(insert_report_data) + ','
    report_data_insert = report_data_insert.rstrip(',')
    fundamental_cursor.execute(report_data_insert)
    fundamental_db.commit()
    #盈利能力数据
    profit_data =tushare.get_profit_data(year,season)
    for k in range(len(profit_data)):
        data = profit_data.iloc[k]
        code = data['code']
        net_profit_ratio = data['net_profit_ratio'] if type(data['net_profit_ratio']) == numpy.float64 else None
        gross_profit_rate = data['gross_profit_rate'] if type(data['gross_profit_rate']) == numpy.float64 else None
        business_income = data['business_income'] if type(data['business_income']) == numpy.float64 else None
        bips = data['bips'] if type(data['bips']) == numpy.float64 else None
        insert_profit_data = (net_profit_ratio,gross_profit_rate,business_income,bips)
        profit_data_insert = "INSERT INTO `{0}` (net_profit_ratio,gross_profit_rate,business_income,bips) VALUES {1} WHERE CODE = {2}".format(table_name,insert_profit_data,code)
        fundamental_cursor.execute(profit_data_insert)
        fundamental_db.commit()

    #营运能力数据
    operation_data = tushare.get_operation_data(year,season)
    for k in range(len(operation_data)):
        data = operation_data.iloc[k]
        code = data['code']
        arturnover = data['arturnover'] if type(data['arturnover']) == numpy.float64 else None
        arturndays = data['arturndays'] if type(data['arturndays']) == numpy.float64 else None
        inventory_turnover = data['inventory_turnover'] if type(data['inventory_turnover']) == numpy.float64 else None
        inventory_days = data['inventory_days'] if type(data['inventory_days']) == numpy.float64 else None
        currentasset_turnover = data['currentasset_turnover'] if type(data['currentasset_turnover']) == numpy.float64 else None
        currentasset_days = data['currentasset_days'] if type(data['currentasset_days']) == numpy.float64 else None
        insert_operation_data = (arturnover,arturndays,inventory_turnover,inventory_days,currentasset_turnover,currentasset_days)
        operation_data_insert = "INSERT INTO `{2}` (arturnover,arturndays,inventory_turnover,inventory_days,currentasset_turnover,currentasset_days) VALUES {0} WHERE CODE = {1}".format(insert_operation_data,code,table_name)
        fundamental_cursor.execute(operation_data_insert)
        fundamental_db.commit()

    #成长能力数据
    growth_data = tushare.get_growth_data(year,season)
    for k in range(len(growth_data)):
        data = growth_data.iloc[k]
        code = data['code']
        mbrg = data['mbrg'] if type(data['mbrg']) == numpy.float64 else None
        nprg = data['nprg'] if type(data['nprg']) == numpy.float64 else None
        nav = data['nav'] if type(data['nav']) == numpy.float64 else None
        targ = data['targ'] if type(data['targ']) == numpy.float64 else None
        epsg = data['epsg'] if type(data['epsg']) == numpy.float64 else None
        seg = data['seg'] if type(data['seg']) == numpy.float64 else None
        insert_growth_data = (mbrg,nprg,nav,targ,epsg,seg)
        growth_data_insert = "INSERT INTO `{2}` (mbrg,nprg,nav,targ,epsg,seg) VALUES {0} WHERE CODE = {1}".format(insert_growth_data,code,table_name)

    #偿债能力数据
    debtpaying_data = tushare.get_debtpaying_data(year,season)
    for k in range(len(debtpaying_data)):
        data = debtpaying_data.iloc[i]
        code = data['code']
        currentratio = data['currentratio'] if type(data['currentratio']) == numpy.float64 else None
        quickratio = data['quickratio'] if type(data['quickratio']) == numpy.float64 else None
        cashratio = data['cashratio'] if type(data['cashratio']) == numpy.float64 else None
        icratio = data['icratio'] if type(data['icratio']) == numpy.float64 else None
        sheqratio = data['sheqratio'] if type(data['sheqratio']) == numpy.float64 else None
        adratio = data['adratio'] if type(data['adratio']) == numpy.float64 else None
        insert_debtpaying_data = (currentratio,quickratio,cashratio,icratio,sheqratio,adratio)
        debtpaying_data_insert = "INSERT INTO `{2}` (currentratio,quickratio,cashratio,icratio,sheqratio,adratio) VALUES {0} WHERE CODE = {1}".format(insert_debtpaying_data,code,table_name)
        fundamental_cursor.execute(debtpaying_data_insert)
        fundamental_db.commit()

    #现金流量
    cashflow_data = tushare.get_cashflow_data(year,season)
    for k in range(len(cashflow_data)):
        data = cashflow_data.iloc[k]
        code = data['code']
        cf_sales = data['cf_sales']
        rateofreturn = data['rateofreturn']
        cf_nm = data['cf_nm']
        cf_liabilities = data['cf_liabilities']
        cashflowratio = data['cashflowratio']
        insert_cashflow_data = (cf_sales,rateofreturn,cf_nm,cf_liabilities,cashflowratio)
        cashflow_data_insert = "INSERT INTO `{2}` (cf_sales,rateofreturn,cf_nm,cf_liabilities,cashflowratio) VALUES {0} WHERE CODE = {1}".format(insert_cashflow_data,code,table_name)
        fundamental_cursor.execute(cashflow_data_insert)
        fundamental_db.commit()



if __name__ == '__main__':
    for i in range(1996,2018):
        for j in range(1,5):
            table_name = str(i) + '0' +str(j)
            fundamental(table_name,i,j)

